SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[usp_StudentSearchRequire]
       (
         @JOL_Require_Company VARCHAR(50) ,
         @JOL_Require_Job VARCHAR(50)
       )
AS 
       SELECT   b.JOL_Employer_Name ,
                a.JOL_Require_ID ,
                a.JOL_Require_Company ,
                a.JOL_Require_Job ,
                a.JOL_Require_VerifyStatus ,
                a.JOL_Require_ClickNumber ,
                a.JOL_Require_AddTime ,
                a.JOL_Require_Study_Level ,
                a.JOL_Require_Profession ,
                a.OnlyForSysu ,
                a.JOL_Require_Number ,
                MAX(a.JOL_Require_VerifyTime)
       FROM     dbo.JOL_Require AS a
       INNER JOIN dbo.JOL_Employer AS b
       ON       a.JOL_Require_Company = b.JOL_Employer_Account
       WHERE    ( b.JOL_Employer_Name LIKE '%' + @JOL_Require_Company + '%' )
                AND ( a.JOL_Require_Job LIKE '%' + @JOL_Require_Job + '%' )
                AND a.JOL_Require_VerifyStatus = '已审核'
                AND a.JOL_Require_Student_Type = 1
                AND a.JOL_Require_EndTime>=GETDATE()
       GROUP BY b.JOL_Employer_Name ,
                a.JOL_Require_ID ,
                a.JOL_Require_Company ,
                a.JOL_Require_Job ,
                a.JOL_Require_VerifyStatus ,
                a.JOL_Require_ClickNumber ,
                a.JOL_Require_AddTime ,
                a.JOL_Require_Study_Level ,
                a.JOL_Require_Profession ,
                a.OnlyForSysu ,
                a.JOL_Require_Number
       ORDER BY MAX(a.JOL_Require_VerifyTime)
GO
